#!/usr/bin/python
# -*- coding: utf-8 -*-

import sys
from sys import exit

from PyQt4 import QtCore, QtGui
from PyQt4.QtCore import Qt
from PyQt4.QtSql import QSqlDatabase, QSqlQuery, QSqlTableModel

def convert(srcFileName, imageHeight, imageField, mobileView='CommemorativeWorld'):
    if imageHeight == 80:
        imageFullHeight = 480
    elif imageHeight == 60:
        imageFullHeight = 360
    else:
        raise

    dstFileName = "%d_%s" % (imageHeight, srcFileName)
        
    srcDb = QSqlDatabase.addDatabase('QSQLITE', "source")
    srcDb.setDatabaseName(srcFileName)
    if not srcDb.open():
        print("Can't open %s" % srcFileName)
        exit(1)

    model = QSqlTableModel(db=srcDb)
    model.setTable('coins')
    model.setFilter("status='owned' OR status='demo'")
    model.select()
    while model.canFetchMore():
        model.fetchMore()
    print(model.rowCount())

    dstDb = QSqlDatabase.addDatabase('QSQLITE', "destination")
    dstDb.setDatabaseName(dstFileName)
    if not dstDb.open():
        print("Can't open %s" % dstFileName)
        exit(1)
    sql = """CREATE TABLE coins (
            id INTEGER NOT NULL PRIMARY KEY AUTOINCREMENT,
            title TEXT,
            value NUMERIC,
            unit TEXT,
            country TEXT,
            year INTEGER,
            period TEXT,
            mint TEXT,
            mintmark TEXT,
            issuedate TEXT,
            type TEXT,
            series TEXT,
            subjectshort TEXT,
            status TEXT,
            material TEXT,
            fineness INTEGER,
            diameter NUMBER,
            thickness NUMBER,
            weight NUMBER,
            grade TEXT,
            quality TEXT,
            mintage INTEGER,
            dateemis TEXT,
            catalognum1 TEXT,
            rarity TEXT,
            price1 NUMERIC,
            price2 NUMERIC,
            price3 NUMERIC,
            price4 NUMERIC,
            subject TEXT,
            image BLOB,
            obverseimg INTEGER,
            reverseimg INTEGER,
            features TEXT,
            createdat TEXT,
            updatedat TEXT,
            quantity INTEGER
        )"""
    QSqlQuery(sql, dstDb)

    sql = """CREATE TABLE images (
            id INTEGER NOT NULL PRIMARY KEY AUTOINCREMENT,
            image BLOB
        )"""
    QSqlQuery(sql, dstDb)

    sql = """CREATE TABLE settings (
            title TEXT NOT NULL UNIQUE,
            value TEXT
        )"""
    QSqlQuery(sql, dstDb)
    sql = """INSERT INTO settings (title, value) VALUES ('Version', 'M2')"""
    QSqlQuery(sql, dstDb)
    sql = "INSERT INTO settings (title, value) VALUES ('mobile_view', '%s')" % mobileView
    QSqlQuery(sql, dstDb)

    sql = """CREATE TABLE updates (
            id INTEGER NOT NULL PRIMARY KEY AUTOINCREMENT,
            version TEXT,
            date TEXT
        )"""
    QSqlQuery(sql, dstDb)

    dstModel = QSqlTableModel(db=dstDb)
    dstModel.setTable('coins')
    dstModel.select()
    if dstModel.rowCount() > 0:
        print("Not empty file %s" % dstFileName)
        exit(1)

    for i in range(model.rowCount()):
        record = model.record(i)
        newRecord = dstModel.record()
#       print(record.value("status"))
        for name in ['title', 'value', 'unit', 'country', 'year', 'period', 'mint',
                     'mintmark', 'issuedate', 'type', 'series', 'subjectshort',
                     'status', 'material', 'fineness', 'diameter', 'thickness',
                     'weight', 'grade', 'quality', 'mintage', 'dateemis',
                     'catalognum1', 'rarity', 'price1', 'price2', 'price3',
                     'price4', 'subject', 'image', 'obverseimg', 'reverseimg',
                     'features', 'createdat', 'updatedat', 'quantity']:
            value = None
            if name == 'image':
                value = record.value(imageField)
                if not record.isNull(imageField):
                    query = QSqlQuery(srcDb)
                    query.prepare("SELECT image FROM images WHERE id==?")
                    query.addBindValue(value)
                    query.exec_()
                    query.first()
                    data = query.record().value(0);
                    image = QtGui.QImage()
                    image.loadFromData(data)
                    ba = QtCore.QByteArray()
                    buffer = QtCore.QBuffer(ba)
                    buffer.open(QtCore.QIODevice.WriteOnly)
                    scaledImage = image.scaled(imageHeight, imageHeight,
                                               Qt.KeepAspectRatio, Qt.SmoothTransformation)
                    scaledImage.save(buffer, 'jpg')
                    value = ba
            elif name in ['obverseimg', 'reverseimg']:
                value = record.value(name)
                if not record.isNull(name):
                    query = QSqlQuery(srcDb)
                    query.prepare("SELECT image FROM images WHERE id==?")
                    query.addBindValue(value)
                    query.exec_()
                    query.first()
                    data = query.record().value(0);
                    image = QtGui.QImage()
                    image.loadFromData(data)
                    if image.width() > imageFullHeight or image.height() > imageFullHeight:
                        ba = QtCore.QByteArray()
                        buffer = QtCore.QBuffer(ba)
                        buffer.open(QtCore.QIODevice.WriteOnly)
                        scaledImage = image.scaled(imageFullHeight, imageFullHeight,
                                                   Qt.KeepAspectRatio, Qt.SmoothTransformation)
                        scaledImage.save(buffer, 'jpg')
                        data = ba
                    
                    query = QSqlQuery(dstDb)
                    query.prepare("INSERT INTO images (image) VALUES (?)")
                    query.addBindValue(data)
                    query.exec_()

                    value = query.lastInsertId()
            else:
                value = record.value(name)

            newRecord.setValue(name, value)
        dstModel.insertRecord(-1, newRecord)

    del model
    srcDb.close()
    del srcDb
    QSqlDatabase.removeDatabase("source")
    del dstModel
    dstDb.close()
    del dstDb
    QSqlDatabase.removeDatabase("destination")
    
    print("Done %s" % dstFileName)

if __name__ == '__main__':
    app = QtGui.QApplication(sys.argv)

    convert('commemorative_ru.db', 80, "reverseimg", 'CommemorativeCountry')
    convert('commemorative_ru.db', 60, "reverseimg", 'CommemorativeCountry')
    convert('full_commemorative_ru.db', 80, "reverseimg", 'CommemorativeCountry')
    convert('full_commemorative_ru.db', 60, "reverseimg", 'CommemorativeCountry')
    convert('commemorative_eu.db', 80, "obverseimg", 'CommemorativeWorld')
    convert('commemorative_eu.db', 60, "obverseimg", 'CommemorativeWorld')
